package com.css.fxfzxczh.modules.fastDisasterAnalysis.casualties.repository;

import com.css.fxfzxczh.base.dict.repository.entity.DictItemEntity;
import com.css.fxfzxczh.common.rest.model.entity.DistrictEntity;
import com.css.fxfzxczh.modules.fastDisasterAnalysis.casualties.entity.PopulationEvaluateModel;
import com.css.fxfzxczh.modules.fastDisasterAnalysis.casualties.entity.PopulationEvaluateResult;
import com.css.fxfzxczh.modules.fastDisasterAnalysis.casualties.entity.ypg.DataPbeInjuries;
import com.css.fxfzxczh.modules.fastDisasterAnalysis.casualties.entity.ypg.DataPbeIntStrArea;
import com.css.fxfzxczh.modules.fastDisasterAnalysis.casualties.vo.CountyIntensityVO;
import com.css.fxfzxczh.modules.fastDisasterAnalysis.houseDestroy.entity.HouseDestroyEvaluateModel;
import com.css.fxfzxczh.util.PlatformObjectUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Dbj
 * @date 2023年07月26日  14:18
 */
@Repository
public class CasualtiesRepository {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public int batchupdate(List<DataPbeIntStrArea> pbeIntStrAreaList) {
        String sql = "insert into data_population_pbe_int_str_area(id,pbe_id,dz_intensity,steel_area,c_area," +
                "b_area,w_area,o_area,destroy,population,covered_area," +
                "province,city,county,town,xsem_id,geom,province_code,city_code,county_code) " +
                " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,ST_GeometryFromText(st_astext(?),4490),?,?,?)";
        System.out.println("开始批量插入");
        int[] updateCountArray = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, pbeIntStrAreaList.get(i).getId());
                ps.setString(2, pbeIntStrAreaList.get(i).getPbeId());
                ps.setString(3, pbeIntStrAreaList.get(i).getDzIntensity());
                ps.setFloat(4, pbeIntStrAreaList.get(i).getSteelArea().floatValue());
                ps.setFloat(5, pbeIntStrAreaList.get(i).getCArea().floatValue());
                ps.setFloat(6, pbeIntStrAreaList.get(i).getBArea().floatValue());
                ps.setFloat(7, pbeIntStrAreaList.get(i).getWArea().floatValue());
                ps.setFloat(8, pbeIntStrAreaList.get(i).getOArea().floatValue());
                ps.setString(9, pbeIntStrAreaList.get(i).getDestroy());
                ps.setFloat(10, pbeIntStrAreaList.get(i).getPopulation().floatValue());
                ps.setFloat(11, pbeIntStrAreaList.get(i).getCoveredArea().floatValue());
                ps.setString(12, pbeIntStrAreaList.get(i).getProvince());
                ps.setString(13, pbeIntStrAreaList.get(i).getCity());
                ps.setString(14, pbeIntStrAreaList.get(i).getCounty());
                ps.setString(15, pbeIntStrAreaList.get(i).getTown());
                ps.setString(16, pbeIntStrAreaList.get(i).getXsemId());
                ps.setString(17, pbeIntStrAreaList.get(i).getGeom());
                ps.setString(18, pbeIntStrAreaList.get(i).getProvinceCode());
                ps.setString(19, pbeIntStrAreaList.get(i).getCityCode());
                ps.setString(20, pbeIntStrAreaList.get(i).getCountyCode());;
            }
            @Override
            public int getBatchSize() {
                return pbeIntStrAreaList.size();
            }
        });
        int sumInsertedCount = 0;
        for (int a : updateCountArray) {
            sumInsertedCount += a;
        }
        return sumInsertedCount;
    }
    public void deleteDataPbeIntStrAreaByXsemId(String xsemId) {
        jdbcTemplate.update("delete from  data_population_pbe_int_str_area  where xsem_id=?", xsemId);
    }

    public void deleteByXsemId(String xsemId) {
        jdbcTemplate.update("delete from  data_pbe_injuries  where xsem_id=?", xsemId);
    }

    public List<DataPbeIntStrArea> getDataPbeIntStrAreaByXsemId(String xsemId, String destroy) {
        String sql = "select * from data_population_pbe_int_str_area where xsem_id='" + xsemId + "'";
        if (PlatformObjectUtils.isNotEmpty(destroy)) {
            sql = sql + " and destroy='" + destroy + "'";
        }
        sql = sql + " order by pbe_id,cast(dz_intensity as int),destroy";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DataPbeIntStrArea.class));
    }

    public int savepbeInjuriesList(List<DataPbeInjuries> list) {
        String sql = "insert into data_pbe_injuries(id,pbe_id,population,coveredarea,death_num,injuries_num,death," +
                "injuries,dz_intentity,xsem_id,geom_text,province,city,county,town,province_code,city_code,county_code) " +
                "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        System.out.println("开始批量插入");
        int[] updateCountArray = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, list.get(i).getId());
                ps.setString(2, list.get(i).getPbeId());
                ps.setInt(3, list.get(i).getPopulation());
                ps.setFloat(4, list.get(i).getCoveredarea().floatValue());
                ps.setFloat(5, list.get(i).getDeathNum().floatValue());
                ps.setFloat(6, list.get(i).getInjuriesNum().floatValue());
                ps.setFloat(7, list.get(i).getDeath().floatValue());
                ps.setFloat(8, list.get(i).getInjuries().floatValue());
                ps.setString(9, list.get(i).getDzIntentity());
                ps.setString(10, list.get(i).getXsemId());
                ps.setString(11, list.get(i).getGeomText());
                ps.setString(12, list.get(i).getProvince());
                ps.setString(13, list.get(i).getCity());
                ps.setString(14, list.get(i).getCounty());
                ps.setString(15, list.get(i).getTown());
                ps.setString(16, list.get(i).getProvinceCode());
                ps.setString(17, list.get(i).getCityCode());
                ps.setString(18, list.get(i).getCountyCode());;
            }
            @Override
            public int getBatchSize() {
                return list.size();
            }
        });
        int sumInsertedCount = 0;
        for (int a : updateCountArray) {
            sumInsertedCount += a;
        }
        return sumInsertedCount;
    }

//    public List<Map<String, Object>> getPbeInjuriesByXsemId(String xsemId) {
//        String sql = "select a.pbe_id pbeId,st_area(st_intersection(st_geometryfromtext(a.geom_text,4490), b.geom)) as area,a.death_num deathNum," +
//                "a.injuries_num injuriesNum,a.province province,a.city city,a.county county,a.province_code provinceCode,a.city_code cityCode,a.county_code countyCode, b.earthquake_level earthquakeLevel,b.fsi_id fsiId " +
//                " from data_pbe_injuries a left join (SELECT xsem_id,intensity,geom,earthquake_level,fsi_id FROM txzch_fast_seismic_influence " +
//                " WHERE xsem_id = '"+xsemId+"' AND del_flag = '0' ) b on a.xsem_id = b.xsem_id " +
//                " where a.dz_intentity=b.intensity and st_intersects(st_geometryfromtext(a.geom_text,4490),b.geom)";
//
//        return jdbcTemplate.queryForList(sql);
//    }

    public List<DataPbeInjuries> getPbeInjuriesByXsemId(String xsemId) {
        String sql = "select * from data_pbe_injuries  where xsem_id='"+xsemId+"'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(DataPbeInjuries.class));
    }

    public String getUnionGeom(String province,String city,String county,String xsemId){
        String sql = "select st_astext(st_union(geom_text)) from data_pbe_injuries  where xsem_id='"+xsemId+"' and province='"+province+"' and city='"+city+"' and county='"+county+"'";
        return jdbcTemplate.queryForObject(sql, String.class);
    }
    public void batchSave(List<PopulationEvaluateResult> resultList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(resultList.toArray());
        String sql = "INSERT INTO xczh_fast_population_evalate_result (" +
                "xfper_id," +
                "xsem_id," +
                "province," +
                "province_code," +
                "city," +
                "city_code," +
                "county," +
                "county_code," +
                "death_num," +
                "death_num_range," +
                "injuries_num," +
                "injuries_num_range," +
                "xfpem_id,geom)" +
                "values (:xfperId,:xsemId,:province,:provinceCode,:city,:cityCode," +
                ":county,:countyCode,:deathNum,:deathNumRange,:injuriesNum,:injuriesNumRange,:xfpemId,ST_GeometryFromText(:geom,4490))";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    public boolean saveEvaluateModel(PopulationEvaluateModel model) {
        String sql="insert into xczh_fast_population_evalate_model(" +
                "xfpem_id," +
                "building_version," +
                "population_version," +
                "influence_version," +
                "fv_id," +
                "model_id," +
                "evaluate_user," +
                "evaluate_time,xsem_id) " +
                "values(:xfpemId, :buildingVersion, :populationVersion, :influenceVersion, :fvId, :modelId, :evaluateUser, :evaluateTime,:xsemId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(model);
        return namedParameterJdbcTemplate.update(sql, source)>0;
    }

    public void deletePopulationEvaluateModel(String xsemId) {
        jdbcTemplate.update("delete from  xczh_fast_population_evalate_model  where xsem_id='"+xsemId+"'" );
    }

    public void deletePopulationEvaluateResult(String xsemId) {
        jdbcTemplate.update("delete from  xczh_fast_population_evalate_result  where xsem_id='"+xsemId+"'");
    }

    public List<PopulationEvaluateResult> getPopulationResult(String xsemId) {
        String sql = "select *,st_astext(geom) as geom from xczh_fast_population_evalate_result where xsem_id='"+xsemId+"'";
        List<PopulationEvaluateResult> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PopulationEvaluateResult.class));
        return PlatformObjectUtils.isNotEmpty(query) && query.size()>0?query:new ArrayList<>();
    }

    public void deleteDataPbeInjuries() {
        jdbcTemplate.update("delete from data_pbe_injuries");
    }

    public void deleteDataPopulationPbeIntStrArea() {
        jdbcTemplate.update("delete from data_population_pbe_int_str_area");
    }

    public PopulationEvaluateModel getModelData(String xsemId) {
        List<PopulationEvaluateModel> query = jdbcTemplate.query("select * from xczh_fast_population_evalate_model where xsem_id='" + xsemId + "'", new BeanPropertyRowMapper<>(PopulationEvaluateModel.class));
        return PlatformObjectUtils.isNotEmpty(query) && query.size()>0?query.get(0):null;
    }

    public List<CountyIntensityVO> getCountyIntensity(String xsemId, String fvId) {
        String sql = "SELECT  a.name,a.code,string_agg(b.intensity::text, ',' ORDER BY b.intensity) AS intensities  " +
                "FROM  district_boundary_county a JOIN (SELECT DISTINCT county_code FROM data_pbe_injuries " +
                "WHERE xsem_id = '"+xsemId+"') AS subquery ON a.code = subquery.county_code  " +
                "JOIN xzch_fast_seismic_influence b ON ST_Intersects(a.geom, b.geom) AND b.fv_id = '"+fvId+"'  " +
                "GROUP BY a.name,a.code ORDER BY a.code;";
        List<CountyIntensityVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CountyIntensityVO.class));
        return PlatformObjectUtils.isNotEmpty(query) && query.size()>0?query:new ArrayList<>();
    }

    public List<DataPbeInjuries> getPbeInjuriesByXsemIdAndCodeAndIntensities(String xsemId, String code, List<String> intensitys) {
        StringBuilder sql = new StringBuilder("select * from data_pbe_injuries  where xsem_id= :xsemId and county_code = :countyCode and dz_intentity in (:intensitys)");
        Map map = new HashMap();
        map.put("intensitys", intensitys);
        map.put("xsemId", xsemId);
        map.put("countyCode", code);
        List<DataPbeInjuries> query = namedParameterJdbcTemplate.query(sql.toString(), map, new BeanPropertyRowMapper<>(DataPbeInjuries.class));
        return PlatformObjectUtils.isNotEmpty(query) && query.size()>0?query:new ArrayList<>();
    }

    public void deleteAssessmentVulnerability(String xsemId, String buildingVersion, String populationVersion) {
        String sql = "delete from  xczh_assessment_vulnerability  where xsem_id=? and building_version = ? and population_version = ?";
        try {
            jdbcTemplate.update(sql, xsemId,buildingVersion,populationVersion);
        } catch (DataAccessException e) {
            throw new RuntimeException(e);
        }
    }
}
